﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Portal.Exam.Model;
using Portal.Web.Utility;
using Portal.Database.Service;
using Portal.Core;
using Portal.Utility;

namespace Portal.Exam.Bussiness
{
    public class ExamCollectionBll
    {
        static string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }


        public static ExamCollectionCls CreateExamCollectionModel(string SiteId, string ExamCollectionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            DataSet dsResult =
                    DBService.GetDataSet(null, "select * from TableExamCollection " +
                                               "     where ExamCollectionId=@ExamCollectionId order by SortIndex",
                    new DbParam[] 
                    { 
                        new DbParam("ExamCollectionId", ExamCollectionId),
                    });
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            ExamCollectionCls OExamCollection = ExamCollectionParser.ParseFromDataRow(dsResult.Tables[0].Rows[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return OExamCollection;
        }



        public static ExamCollectionCls[] ReadingExamCollection(string SiteId, string ParentExamCollectionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string Query = "select * from TableExamCollection where AssetCode='" + AssetCode + "' ";
            if (!string.IsNullOrEmpty(ParentExamCollectionId))
            {
                Query += " and frkParentExamCollectionId='" + ParentExamCollectionId + "'";
            }
            else 
            {
                Query += " and frkParentExamCollectionId is null";
            }
            Query += " order by SortIndex";
            DataSet dsResult = DBService.GetDataSet(null, Query, null);
            if (dsResult.Tables[0].Rows.Count == 0)
            {
                return new ExamCollectionCls[0];
            }
            ExamCollectionCls[] ExamCollection = ExamCollectionParser.ParseFromDataTable(dsResult.Tables[0]);
            dsResult.Clear();
            dsResult.Dispose();
            return ExamCollection;
        }




        //public static ExamCollectionCls[] ReadingExamCollectionForUser(string SiteId, string GroupDataId, string UserId)
        //{
        //    IDbService DBService = WebEnvironments.CreateDBService(SiteId, GetExamConnectionString(SiteId));
        //    string Query = " select * from TableExamCollection where frkGroupDataId='" + GroupDataId + "' and frkParentExamCollectionId is null "+
        //                   "    and "+
        //                   "    ("+
        //                   "        ExamCollectionId in "+
        //                   "        ("+
        //                   "            select ObjectId from TablePermissionRole"+
        //                   "                where frkPermissionTypeId='DocumentFolder' and type='user' and frkUserId='"+UserId+"' "+
        //                   "        )"+
        //                   "        or"+
        //                   "        ExamCollectionId in"+
        //                   "        ("+
        //                   "            select ObjectId from TablePermissionRole" +
        //                   "                where frkPermissionTypeId='DocumentFolder' and type='role'" +
        //                   "                and frkRoleId in "+
        //                   "                ("+
        //                   "                    select frkRoleId from TableUserRole"+
        //                   "                        where TableUserRole.frkUserId='"+UserId+"'"+
        //                   "                )"+
        //                   "        )"+
        //                   "    )"+
        //                   "  order by SortIndex";
        //    DataSet dsResult = DBService.GetDataSet(null, Query, null);
        //    if (dsResult.Tables[0].Rows.Count == 0)
        //    {
        //        return new ExamCollectionCls[0];
        //    }
        //    ExamCollectionCls[] ExamCollection = ExamCollectionParser.ParseFromDataTable(dsResult.Tables[0]);
        //    dsResult.Clear();
        //    dsResult.Dispose();
        //    return ExamCollection;
        //}

        public static string AddExamCollection(
            string SiteId,
            string ExamCollectionName,
            string Description,
            string RootExamCollectionId,
            string ParentExamCollectionId,
            string SortIndex)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string ExamCollectionId = System.Guid.NewGuid().ToString();
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            if (string.IsNullOrEmpty(ParentExamCollectionId))
            {
                ParentExamCollectionId = null;
            }
            if (string.IsNullOrEmpty(RootExamCollectionId))
            {
                RootExamCollectionId = null;
            }
            DBService.Insert(null, "TableExamCollection",
                    new DbParam[] 
                    { 
                        new DbParam("ExamCollectionId", ExamCollectionId),
                        new DbParam("AssetCode", AssetCode),
                        new DbParam("ExamCollectionName", ExamCollectionName),
                        new DbParam("Description", Description),
                        new DbParam("frkParentExamCollectionId", ParentExamCollectionId),
                        new DbParam("RootExamCollectionId", RootExamCollectionId),
                        new DbParam("SortIndex", SortIndex),
                    });
            return ExamCollectionId;
        }


        public static void SaveExamCollection(string SiteId, string ExamCollectionId, string ExamCollectionName, string Description, string SortIndex)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            DBService.Update(null, "TableExamCollection", "ExamCollectionId", ExamCollectionId,
                    new DbParam[] 
                    { 
                        new DbParam("ExamCollectionName", ExamCollectionName),
                        new DbParam("Description", Description),
                        new DbParam("SortIndex", SortIndex),
                    });
        }


        public static void DeleteExamCollection(string SiteId, string ExamCollectionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query =
                " Delete from TableExamCollectionQuestion where frkExamCollectionId=@ExamCollectionId; "+
                " Delete from TableExamCollection where ExamCollectionId=@ExamCollectionId; ";
            DBService.ExecuteNonQuery(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("ExamCollectionId", ExamCollectionId),
                    });
        }



        public static DataTable ReadingQuestionForExamCollection(string SiteId, string ExamCollectionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query = " select TableQuestion.* from TableExamCollectionQuestion "+
                           "        join TableQuestion on TableQuestion.QuestionId=frkExamQuestionId"+
                           "  where frkExamCollectionId='" + ExamCollectionId+"'";

            DataTable dtResult = DBService.GetDataTable(null, Query);
            return dtResult;
        }

        public static DataTable ReadingQuestionForExamCollection(string SiteId, string ExamCollectionId,string Keyword)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query = " select TableQuestion.* from TableExamCollectionQuestion " +
                           "        join TableQuestion on TableQuestion.QuestionId=frkExamQuestionId" +
                           "  where frkExamCollectionId='" + ExamCollectionId + "'";
            if (!string.IsNullOrEmpty(Keyword))
            {
                Keyword = FunctionUtilities.CorrectForSearch(Keyword);
                Query += " and Title like N'%" + Keyword + "%'";
            }

            DataTable dtResult = DBService.GetDataTable(null, Query);
            return dtResult;
        }

        public static void RemoveCollectionFromExam(string SiteId, string ExamCollectionId, string QuestionId)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId, GetExamConnectionString(SiteId));
            string Query = " Delete from TableExamCollectionQuestion " +
                           "    where frkExamCollectionId='" + ExamCollectionId + "' and frkExamQuestionId='"+QuestionId+"'";

            DBService.ExecuteNonQuery(null, Query, null);
        }
    }
}
